/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.dao;

import com.kallsonys.service.Campaign;
import com.kallsonys.service.ElementList;
import com.kallsonys.service.ElementListPage;
import com.kallsonys.service.Item;
import com.kallsonys.service.Product;
import com.kallsonys.service.Range;
import com.kallsonys.service.StringPage;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class ProductDAO {

    private static String GET_CATEGORYS = "{ call PKG_CURDS.GET_CATEGORYS (?) }";
    private static String GET_PRODUCT_ID = "{ call PKG_CURDS.GET_PRODUCT_ID (?, ?) }";
    private static String GET_PRODUCT_DESCRIPTION = "{ call PKG_CURDS.GET_PRODUCT_DESCRIPTION (?, ?, ?, ?) }";
    private static String GET_PRODUCT_CATEGORY = "{ call PKG_CURDS.GET_PRODUCT_CATEGORY (?, ?, ?, ?) }";
    private static String GET_PRODUCT_NAME = "{ call PKG_CURDS.GET_PRODUCT_NAME (?, ?, ?, ?) }";
    private static String GET_PRODUCT_RANKING = "{ call PKG_CURDS.GET_PRODUCT_RANKING (?, ?, ?) }";
    private static String GET_CAMPAIGNS = "{ call PKG_CURDS.GET_CAMPAIGNS (?) }";
    private static String GET_TOP5_RELATED_PRODUCTS = "{ call PKG_CURDS.GET_TOP5_RELATED_PRODUCTS (?, ?) }";
    private static String GET_NEWEST_PRODUCTS = "{ call PKG_CURDS.GET_NEWEST_PRODUCTS (?) }";
    private DataSource ds;

    public ProductDAO(DataSource ds) {
        this.ds = ds;
    }
    //getAllCategorys

    public List<Item> getAllCategorys() throws Exception {
        List<Item> retorno = new ArrayList<Item>();


        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CATEGORYS);


            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.execute();



            Object o = stmt.getObject(1);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Item objItem = new Item();
                    objItem.setId(rs.getString("CATEGORYID"));
                    objItem.setValue(rs.getString("CATEGORYDESCRIPTION"));

                    retorno.add(objItem);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public Product getProductsById(long idProduct) throws Exception {
        Product retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_PRODUCT_ID);

            stmt.setLong(1, idProduct);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {

                    retorno = new Product();
                    retorno.setId(idProduct);
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));
                    retorno.setProducer(rs.getString("PRODUCTPRODUCER"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public ElementListPage<Product> getProductsByDescription(StringPage stringPage) throws Exception {
        ElementListPage<Product> productList = new ElementListPage<Product>();
        productList.setElementList(new ArrayList<Product>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_PRODUCT_DESCRIPTION);

            stmt.setString(1, stringPage.getRequest());
            stmt.setInt(2, stringPage.getPage());
            stmt.registerOutParameter(3, OracleTypes.INTEGER);
            stmt.registerOutParameter(4, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(4);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Product retorno = new Product();
                    retorno.setId(rs.getLong("PRODUCTID"));
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));
                    retorno.setProducer(rs.getString("PRODUCTPRODUCER"));

                    productList.getElementList().add(retorno);
                }

                productList.setPages(stmt.getInt(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return productList;
    }

    public ElementListPage<Product> getByIdCategory(StringPage stringPage) throws Exception {
        ElementListPage<Product> productList = new ElementListPage<Product>();
        productList.setElementList(new ArrayList<Product>());


        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_PRODUCT_CATEGORY);

            stmt.setString(1, stringPage.getRequest());
            stmt.setInt(2, stringPage.getPage());
            stmt.registerOutParameter(3, OracleTypes.INTEGER);
            stmt.registerOutParameter(4, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(4);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Product retorno = new Product();
                    retorno.setId(rs.getLong("PRODUCTID"));
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));
                    retorno.setProducer(rs.getString("PRODUCTPRODUCER"));

                    productList.getElementList().add(retorno);
                }

                productList.setPages(stmt.getInt(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return productList;
    }

    public ElementListPage<Product> getProductsByName(StringPage stringPage) throws Exception {
        ElementListPage<Product> productList = new ElementListPage<Product>();
        productList.setElementList(new ArrayList<Product>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_PRODUCT_NAME);

            stmt.setString(1, stringPage.getRequest());
            stmt.setInt(2, stringPage.getPage());
            stmt.registerOutParameter(3, OracleTypes.INTEGER);
            stmt.registerOutParameter(4, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(4);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Product retorno = new Product();
                    retorno.setId(rs.getLong("PRODUCTID"));
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));
                    retorno.setProducer(rs.getString("PRODUCTPRODUCER"));

                    productList.getElementList().add(retorno);
                }

                productList.setPages(stmt.getInt(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return productList;
    }

    public List<Product> getProductsRelated(long idProduct) throws Exception {
        List<Product> lstRetorno = new ArrayList<Product>();
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_TOP5_RELATED_PRODUCTS);

            stmt.setLong(1, idProduct);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Product retorno = new Product();
                    retorno.setId(rs.getLong("PRODUCTID"));
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));
                    retorno.setProducer(rs.getString("PRODUCTPRODUCER"));

                    lstRetorno.add(retorno);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return lstRetorno;
    }

    public List<Product> getProductsNewest() throws Exception {
        List<Product> lstRetorno = new ArrayList<Product>();
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_NEWEST_PRODUCTS);

            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(1);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Product retorno = new Product();
                    retorno.setId(rs.getLong("PRODUCTID"));
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));
                    retorno.setProducer(rs.getString("PRODUCTPRODUCER"));

                    lstRetorno.add(retorno);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return lstRetorno;
    }

    public ElementList<Product> getProductsByRanking(Range range) throws Exception {

        ElementList<Product> list = new ElementList<Product>();


        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_PRODUCT_RANKING);

            String fechaInicial = String.valueOf(range.getStartDate().getYear()) + "/"
                    + String.valueOf(range.getStartDate().getMonth() < 10 ? ("0" + String.valueOf(range.getStartDate().getMonth())) : String.valueOf(range.getStartDate().getMonth())) + "/"
                    + String.valueOf(range.getStartDate().getDay() < 10 ? ("0" + String.valueOf(range.getStartDate().getDay())) : String.valueOf(range.getStartDate().getDay()));

            String fechaFinal = String.valueOf(range.getEndDate().getYear()) + "/"
                    + String.valueOf(range.getEndDate().getMonth() < 10 ? ("0" + String.valueOf(range.getEndDate().getMonth())) : String.valueOf(range.getEndDate().getMonth())) + "/"
                    + String.valueOf(range.getEndDate().getDay() < 10 ? ("0" + String.valueOf(range.getEndDate().getDay())) : String.valueOf(range.getEndDate().getDay()));

            stmt.setString(1, fechaInicial);
            stmt.setString(2, fechaFinal);
            stmt.registerOutParameter(3, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(3);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Product retorno = new Product();
                    retorno.setId(rs.getLong("PRODUCTID"));
                    retorno.setCategory(rs.getString("PRODUCTCATEGORY"));
                    retorno.setDescription(rs.getString("PRODUCTDESCRIPTION"));
                    retorno.setEnable(rs.getString("PRODUCTENABLE").equals("1"));
                    retorno.setIdProduct(rs.getLong("PRODUCTIDPRODUCT"));
                    retorno.setImageurl(rs.getString("PRODUCTIMAGEURL"));
                    retorno.setListPrice(rs.getString("PRODUCTLIST_PRICE"));
                    retorno.setName(rs.getString("PRODUCTNAME"));

                    /* Valor de Total de Ranking */
                    retorno.setProducer(String.valueOf(rs.getLong("Total")));

                    list.getLstElement().add(retorno);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return list;
    }

    public List<Campaign> getAllCampaign() throws Exception {
        List<Campaign> lstRetorno = new ArrayList<Campaign>();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CAMPAIGNS);


            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.execute();



            Object o = stmt.getObject(1);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Campaign objItem = new Campaign();
                    objItem.setDate(rs.getDate("CAMPAIGNDATE"));
                    objItem.setDescription(rs.getString("CAMPAIGNDESCRIPTION"));
                    objItem.setId(rs.getLong("CAMPAIGNID"));
                    objItem.setImageUrl(rs.getString("CAMPAIGNIMAGEURL"));
                    objItem.setProductId(rs.getLong("PRODUCTID"));

                    lstRetorno.add(objItem);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return lstRetorno;
    }
}
